sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE SMALLFILE TABLESPACE HR_APP DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/hr_app01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

ALTER TABLESPACE HR_APP ADD DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/hr_app02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

CREATE BIGFILE TABLESPACE SALES_APP DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/sales_app.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

sql / as sysdba

SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES ORDER BY 1;

ALTER SESSION SET CONTAINER=PDB1;

SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES ORDER BY 1;

SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB, MAXBYTES/1024/1024/1024 MAX_GB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='HR_APP';

SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB, MAXBYTES/1024/1024/1024 MAX_GB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='SALES_APP';



Opcja AUTOEXTEND. Kiedy warto na niej polegać, a kiedy lepiej z niej zrezygnować?


ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/hr_app01.dbf' RESIZE 10G;




Oracle Managed Files (OMF)


SHOW PARAMETERS DB_CREATE_FILE_DEST

ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata' SCOPE=BOTH;

SHOW PARAMETERS DB_CREATE_FILE_DEST

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE TABLESPACE SALES_APP2;

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='SALES_APP2';



Shrink Tablespace

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE BIGFILE TABLESPACE HR_APP_SHRINK_TBS DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/HR_APP_SHRINK_TBS01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='HR_APP_SHRINK_TBS';

CREATE USER HR_APP_SHRINK_USER IDENTIFIED BY Password_1 DEFAULT TABLESPACE HR_APP_SHRINK_TBS QUOTA UNLIMITED ON HR_APP_SHRINK_TBS;

GRANT CONNECT, DB_DEVELOPER_ROLE, SELECT_CATALOG_ROLE TO HR_APP_SHRINK_USER;

sql HR_APP_SHRINK_USER@//localhost:1521/pdb1

CREATE TABLE EMPLOYEE_DATA_TBSTEST (
    employee_id    NUMBER(10),
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    hire_date      DATE,
    salary         NUMBER(10,2)
);


CREATE TABLE EMPLOYEE_DATA_TBSTEST2 (
    employee_id    NUMBER(10),
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    hire_date      DATE,
    salary         NUMBER(10,2)
);


BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO EMPLOYEE_DATA_TBSTEST (employee_id, first_name, last_name, hire_date, salary)
        VALUES (i, 'Jan' || i, 'Kowalski' || i, SYSDATE - DBMS_RANDOM.VALUE(0, 3650), DBMS_RANDOM.VALUE(3000, 10000));
    END LOOP;
    COMMIT;
END;
/


BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO EMPLOYEE_DATA_TBSTEST2 (employee_id, first_name, last_name, hire_date, salary)
        VALUES (i, 'Jan' || i, 'Kowalski' || i, SYSDATE - DBMS_RANDOM.VALUE(0, 3650), DBMS_RANDOM.VALUE(3000, 10000));
    END LOOP;
    COMMIT;
END;
/


SELECT COUNT(*) FROM EMPLOYEE_DATA_TBSTEST;

SELECT COUNT(*) FROM EMPLOYEE_DATA_TBSTEST2;

SELECT TABLESPACE_NAME, BLOCKS, BYTES/1024/1024 AS SIZE_MB FROM  DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HR_APP_SHRINK_TBS';

SELECT OWNER, SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='EMPLOYEE_DATA_TBSTEST';

SELECT OWNER, SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='EMPLOYEE_DATA_TBSTEST2';

TRUNCATE TABLE EMPLOYEE_DATA_TBSTEST2;

SELECT TABLESPACE_NAME, BLOCKS, BYTES/1024/1024 AS SIZE_MB FROM   DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HR_APP_SHRINK_TBS';

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

SET SERVEROUTPUT ON

EXECUTE DBMS_SPACE.SHRINK_TABLESPACE('HR_APP_SHRINK_TBS', SHRINK_MODE => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE);

EXECUTE DBMS_SPACE.SHRINK_TABLESPACE('HR_APP_SHRINK_TBS');

SELECT TABLESPACE_NAME, BLOCKS, BYTES/1024/1024 AS SIZE_MB FROM   DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HR_APP_SHRINK_TBS';




Zarządzanie przestrzenią wycofania

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';

SELECT f.tablespace_name,
       f.size_mb,
       NVL(t.usage_mb,0) AS usage_mb,
       (f.size_mb - NVL(t.usage_mb,0)) AS free_mb
FROM (
    SELECT t.tablespace_name,
           SUM(df.bytes)/1024/1024 AS size_mb
    FROM   DBA_TABLESPACES t
           JOIN DBA_DATA_FILES df
             ON df.tablespace_name = t.tablespace_name
    WHERE  t.contents = 'UNDO'
    GROUP  BY t.tablespace_name
) f
LEFT JOIN (
    SELECT e.tablespace_name,
           SUM(e.bytes)/1024/1024 AS usage_mb
    FROM   DBA_UNDO_EXTENTS e
    WHERE  e.status <> 'EXPIRED'
    GROUP  BY e.tablespace_name
) t
  ON t.tablespace_name = f.tablespace_name;


SELECT S.SID, S.USERNAME, S.MACHINE, T.USED_UREC, T.USED_UBLK FROM V$SESSION S, V$TRANSACTION T WHERE S.SADDR = T.SES_ADDR ORDER BY T.USED_UBLK DESC;


SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB, MAXBYTES/1024/1024/1024/1024 MAX_TB, AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='UNDOTBS1' ORDER BY FILE_NAME;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/undotbs01.dbf' RESIZE 10G;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB01/pdb1/undotbs01.dbf' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;




Zarządzanie przestrzenią tymczasową


SELECT sess.sid,
       tsu.username,
       tsu.blocks * tblsp.block_size / 1024 / 1024 AS mb_used,
       tsu.tablespace,
       tsu.sql_id,
       sq.sql_text
FROM   V$TEMPSEG_USAGE tsu
       JOIN V$SESSION sess
         ON tsu.session_addr = sess.saddr
       LEFT JOIN V$SQL sq
         ON tsu.sql_id = sq.sql_id
       JOIN DBA_TABLESPACES tblsp
         ON tsu.tablespace = tblsp.tablespace_name;



SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, STATUS, AUTOEXTENSIBLE, MAXBYTES/1024/1024/1024 MAX_GB FROM DBA_TEMP_FILES;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/DB01/pdb1/temp02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, STATUS, AUTOEXTENSIBLE, MAXBYTES/1024/1024/1024 MAX_GB FROM DBA_TEMP_FILES;





Zarządzanie plikami dziennika powtórzeń


sql / as sysdba

SELECT GROUP#, TYPE, MEMBER FROM V$LOGFILE ORDER BY GROUP#;

mkdir -p /u01/app/oracle/oradata/DB01/redo01

mkdir -p /u01/app/oracle/oradata/DB01/redo02

chmod 775 /u01/app/oracle/oradata/DB01/redo0?


ALTER DATABASE ADD LOGFILE GROUP 1
   ('/u01/app/oracle/oradata/DB01/redo01/redo01_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo01_b.log') size 200M;

ALTER DATABASE ADD LOGFILE GROUP 2 
   ('/u01/app/oracle/oradata/DB01/redo01/redo02_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo02_b.log') size 200M;

ALTER DATABASE ADD LOGFILE GROUP 3 
   ('/u01/app/oracle/oradata/DB01/redo01/redo03_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo03_b.log') size 200M;

ALTER DATABASE ADD LOGFILE GROUP 4 
   ('/u01/app/oracle/oradata/DB01/redo01/redo04_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo04_b.log') size 200M;

ALTER DATABASE ADD LOGFILE GROUP 5 
   ('/u01/app/oracle/oradata/DB01/redo01/redo05_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo05_b.log') size 200M;

ALTER DATABASE ADD LOGFILE GROUP 6 
   ('/u01/app/oracle/oradata/DB01/redo01/redo06_a.log',
    '/u01/app/oracle/oradata/DB01/redo02/redo06_b.log') size 200M;


SELECT GROUP#, BYTES, STATUS FROM V$LOG;

ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#, BYTES, STATUS FROM V$LOG;

ALTER SYSTEM CHECKPOINT;

SELECT GROUP#, BYTES, STATUS FROM V$LOG;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SELECT GROUP#, TYPE, MEMBER FROM V$LOGFILE ORDER BY GROUP#;





Plik orapw<SID>


cd $ORACLE_HOME/dbs

ls -l orapw*

mv orapwdb01 orapwdb01_OLD

SELECT FILE_NAME FROM V$PASSWORDFILE_INFO;

orapwd file=orapwdb01

SHOW PARAMETER remote_login_passwordfile




Automatic Diagnostic Repository i plik alert logu


SHOW PARAMETERS DIAGNOSTIC_DEST

SELECT NAME, VALUE FROM V$DIAG_INFO;



Automatic Diagnostic Repository Command Interpreter

adrci

show homes

set homepath diag/rdbms/db01/db01

show incydent

show problem

show incident -mode DETAIL -p "inc_id=12345"

ips create package

ips add incident 12345 package 1

ips generate package 1 in '/tmp'



Alert log

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

ln -s /u01/app/oracle/diag/rdbms/db01/db01/trace/alert_db01.log ~/alert_db01.log


cd

ls -l alert_db01.log

less alert_db01.log












